<?php
class Fpt_Business_ClubFavorite_Adapter_MySql extends Fpt_Business_ClubFavorite_Adapter_Abstract
{
    /**
     * Instance
     */
    protected static $_instance = null;

    /**
     * Constructor of class
     */
    public function __construct()
    {

    }

    /**
     * Get singletom instance
     */
    public final static function getInstance()
    {
        //Check instance
        if ( is_null(self::$_instance) )
        {
            self::$_instance    = new self();
        }//end if

        //Return instance
        return self::$_instance;
    }

    /**
     * Get list my post
     */
    public function getListMyPost($userId)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getListMyPost(:p_userid);');
            $stmt->bindParam('p_userid', $userId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array();
        }//end try catch
    }

    /**
     * Get list comment
     */
    public function getListComment($articleId)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getArticleComment(:p_article);');
            $stmt->bindParam('p_article', $articleId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array();
        }//end try catch
    }

    /**
     * Insert comment
     */
    public function insertComment($params)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt   = $clubMaster->prepare('CALL sp_clubs_insertComment(:p_userid, :p_articleid, :p_email, :p_fullname, :p_website, :p_content, @p_commentid);');
            $stmt->bindParam('p_userid', $params['userid'], PDO::PARAM_INT);
            $stmt->bindParam('p_articleid', $params['articleid'], PDO::PARAM_INT);
            $stmt->bindParam('p_email', $params['email']);
            $stmt->bindParam('p_fullname', $params['fullname']);
            $stmt->bindParam('p_website', $params['website']);
            $stmt->bindParam('p_content', $params['content']);
            $stmt->execute();

            //Select output
            $outputObj  = $clubMaster->query('SELECT @p_commentid AS commentid');
            $result     = $outputObj->fetch();

            //Closed cursor
            $stmt->closeCursor();
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return (isset($result['commentid']) && is_numeric($result['commentid']) && $result['commentid']>0)?$result['commentid']:0;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return 0;
        }//end try catch

        //Return
        return 0;
    }

    /**
     * Get article by category and status
     */
    public function getArticleByCategoryAndStatus($category, $status)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getListNewPost(:p_cateid, :p_status);');
            $stmt->bindParam('p_cateid', $category, PDO::PARAM_INT);
            $stmt->bindParam('p_status', $status, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array();
        }//end try catch
    }

    /**
     * Get article hot by admin set
     */
    public function getArticleHotByAdminSet($category, $limit, $offset)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getHotNews(:p_cateid, :p_limit, :p_offset, @p_rowcount);');
            $stmt->bindParam('p_cateid', $category, PDO::PARAM_INT);
            $stmt->bindParam('p_limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam('p_offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();

            //Select output
            $outputObj  = $clubSlave->query('SELECT @p_rowcount AS total');
            $arrTotal   = $outputObj->fetch();

            //Closed cursor
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return array('list'=>$result, 'total'=>$arrTotal['total']);
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array('list'=>null, 'total'=>0);
        }//end try catch
    }

    /**
     * Get article hot by view & comment
     */
    public function getArticleHotByViewComment($orderBy, $limit, $offset)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getHotByViewComment(:p_orderby, :p_limit, :p_offset, @p_rowcount);');
            $stmt->bindParam('p_orderby', $orderBy);
            $stmt->bindParam('p_limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam('p_offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();

            //Select output
            $outputObj  = $clubSlave->query('SELECT @p_rowcount AS total');
            $arrTotal   = $outputObj->fetch();

            //Closed cursor
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return array('list'=>$result, 'total'=>$arrTotal['total']);
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array('list'=>null, 'total'=>0);
        }//end try catch
    }

    /**
     * Get article detail
     */
    public function getArticleDetail($articleId)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getDetailArticles(:p_articleid);');
            $stmt->bindParam('p_articleid', $articleId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array();
        }//end try catch
    }

    /**
     * Update article status
     */
    public function updateArticleStatus($articleId, $status)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_updateArticlesByStatus(:p_articleid, :p_status);');
            $stmt->bindParam('p_articleid', $articleId, PDO::PARAM_INT);
            $stmt->bindParam('p_status', $status, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return (isset($result['result']) && $result['result']==1)?true:false;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return false;
        }//end try catch
    }

    /**
     * Insert page view
     */
    public function insertPageView($params)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_insertArticleView(:p_userid, :p_articleid, :p_loginip, @p_viewid);');
            $stmt->bindParam('p_userid', $params['userid'], PDO::PARAM_INT);
            $stmt->bindParam('p_articleid', $params['articleid'], PDO::PARAM_INT);
            $stmt->bindParam('p_loginip', $params['loginip']);
            $stmt->execute();

            //Select output
            $outputObj  = $clubMaster->query('SELECT @p_viewid AS viewid');
            $result     = $outputObj->fetch();

            //Closed cursor
            $stmt->closeCursor();
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return (isset($result['viewid']) && is_numeric($result['viewid']) && $result['viewid']>0)?true:false;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return false;
        }//end try catch
    }

    /**
     * Get article artistic
     */
    public function getArticleStatistic($articleId)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getArticleStatistic(:p_articleid);');
            $stmt->bindParam('p_articleid', $articleId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array();
        }//end try catch
    }

    /**
     * Insert article
     */
    public function insertArticle($params)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt   = $clubMaster->prepare('CALL sp_clubs_insertArticle(:p_userid, :p_usertype, :p_categoryid, :p_title, :p_content, :p_status, :p_image, :p_url, :p_home, :p_award, :p_info, :p_coachman, @p_articleid);');
            $stmt->bindParam('p_userid', $params['userid'], PDO::PARAM_INT);
            $stmt->bindParam('p_usertype', $params['user_type'], PDO::PARAM_INT);
            $stmt->bindParam('p_categoryid', $params['categoryid'], PDO::PARAM_INT);
            $stmt->bindParam('p_title', $params['title']);
            $stmt->bindParam('p_content', $params['content']);
            $stmt->bindParam('p_status', $params['status'], PDO::PARAM_INT);
            $stmt->bindParam('p_image', $params['image']);
            $stmt->bindParam('p_url', $params['url']);
            $stmt->bindParam('p_home', $params['home']);
            $stmt->bindParam('p_award', $params['award']);
            $stmt->bindParam('p_info', $params['info']);
            $stmt->bindParam('p_coachman', $params['coachman']);
            $stmt->execute();

            //Select output
            $outputObj  = $clubMaster->query('SELECT @p_articleid AS articleid');
            $result     = $outputObj->fetch();

            //Closed cursor
            $stmt->closeCursor();
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return (isset($result['articleid']) && is_numeric($result['articleid']) && $result['articleid']>0)?$result['articleid']:0;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return 0;
        }//end try catch
        
        //Return
        return 0;
    }

    /**
     * Check user vote
     */
    public function checkUserVote($articleId, $userId)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_checkUserVote(:p_articleid, :p_userid);');
            $stmt->bindParam('p_articleid', $articleId, PDO::PARAM_INT);
            $stmt->bindParam('p_userid', $userId, PDO::PARAM_INT);
            $stmt->execute();
            $result     = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return 0;
        }//end try catch

        //Return
        return 0;
    }

    /**
     * Check user vote by email
     */
    public function checkUserVoteByEmail($articleId, $email)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_checkEmailVote(:p_articleid, :p_email);');
            $stmt->bindParam('p_articleid', $articleId, PDO::PARAM_INT);
            $stmt->bindParam('p_email', $email);
            $stmt->execute();
            $result     = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return $result;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return 0;
        }//end try catch

        //Return
        return 0;
    }

    /**
     * Insert vote
     */
    public function insertVote($params)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt   = $clubMaster->prepare('CALL sp_clubs_insertVote(:p_articleid, :p_article_type, :p_userid, :p_ownerid, :p_email, @p_voteid);');
            $stmt->bindParam('p_articleid', $params['articleid'], PDO::PARAM_INT);
            $stmt->bindParam('p_article_type', $params['article_type'], PDO::PARAM_INT);
            $stmt->bindParam('p_userid', $params['userid'], PDO::PARAM_INT);
            $stmt->bindParam('p_ownerid', $params['ownerid'], PDO::PARAM_INT);
            $stmt->bindParam('p_email', $params['email']);
            $stmt->execute();

            //Select output
            $outputObj  = $clubMaster->query('SELECT @p_voteid AS voteid');
            $result     = $outputObj->fetch();

            //Closed cursor
            $stmt->closeCursor();
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return (isset($result['voteid']) && is_numeric($result['voteid']) && $result['voteid']>0)?$result['voteid']:0;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return 0;
        }//end try catch

        //Return
        return 0;
    }
	/*
     * get all list articles
     * @param <array> $para, <int> $limit, <int> $offset
     * return <array> $result
     */
    public function getArticlesInfo($para,$limit, $offset)
    {
        try
        {
            //Get db
            $where ='';
            $clubMaster = Fpt_Global::getClubMasterDB();
            $where = "WHERE 1=1";
            if(isset($para['categoryid']) && ($para['categoryid'] !=3) )
            {
                $where .= ' AND categoryid = '.$para['categoryid'];
            }

            if(!empty($para['status']))
            {
                $where .= ' AND status = '.$para['status'];
            }
            else
            {
                $where .= ' AND status <> 0';
            }
            $where .= " ORDER BY creation_time DESC";

            $stmt       = $clubMaster->prepare('CALL sp_clubs_getArticlesInfo(:p_condition,:p_limit, :p_offset, @p_rowcount);');
            $stmt->bindParam('p_condition', $where);
            $stmt->bindParam('p_limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam('p_offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();
            //Fetch data
            $stmt = $clubMaster->query("SELECT @p_rowcount");
            $total = $stmt->fetchColumn();
            $response = array('rows'=>$result, 'total'=>$total);
        }
         catch (Exception $e)
         {
            ClubFavorite::logError($e);
            $response = array('rows'=>array(), 'total'=>0);
         }
         return $response;
    }
    /*
     * insert data for hot news
     * @param: <array> $param
     */
    public function insertHotNews($params)
    {
        try
        {            
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt   = $clubMaster->prepare('CALL sp_clubs_insertHotNews(:p_articleid, :p_userpost, :p_order, :p_cateid, @p_hotid);');
            $stmt->bindParam('p_articleid', $params['articleid'], PDO::PARAM_INT);
            $stmt->bindParam('p_userpost', $params['userpost'], PDO::PARAM_INT);
            $stmt->bindParam('p_order', $params['display_order'], PDO::PARAM_INT);
            $stmt->bindParam('p_cateid', $params['categoryid'],PDO::PARAM_INT);
            $stmt->execute();

            //Select output
            $obj  = $clubMaster->query('SELECT @p_hotid AS hotid');
            $result     = $obj->fetch();

            $stmt->closeCursor();
            $obj->closeCursor();

            //Unset
            unset($stmt);
            unset($obj);

            //Return
            return (isset($result['hotid']) && is_numeric($result['hotid']) && $result['hotid']>0)?$result['hotid']:0;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return 0;
        }//end try catch

        //Return
        return 0;
    }

    /**
     * Delete article hot
     */
    public function deleteHotNews($articleId)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_deleteHotNews(:p_articleid);');
            $stmt->bindParam('p_articleid', $articleId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return (isset($result['result']) && $result['result']==1)?true:false;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return false;
        }//end try catch
    }

    /*
     * get list hot news
     * @param <array> $para, <int> $limit, <int> $offset
     * return <array> $result
     */
    public function getListHotNews()
    {
        try
        {
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_getListHotNews();');            
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();
            return $result;
        }
         catch (Exception $e)
         {
            ClubFavorite::logError($e);
            return array();
         }
         return $result;
    }
    /*
     * update article
     * @param <array> $params
     * return <int> $result
     */
    public function updateArticle($params)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt   = $clubMaster->prepare('CALL sp_clubs_updateArticles(:p_articleid, :p_userid, :p_usertype, :p_title, :p_content, :p_image, :p_url, :p_status, :p_home, :p_award, :p_info, :p_coachman);');
            $stmt->bindParam('p_articleid', $params['articleid'], PDO::PARAM_INT);
            $stmt->bindParam('p_userid', $params['userid'], PDO::PARAM_INT);
            $stmt->bindParam('p_usertype', $params['user_type'], PDO::PARAM_INT);            
            $stmt->bindParam('p_title', $params['title']);
            $stmt->bindParam('p_content', $params['content']);            
            $stmt->bindParam('p_image', $params['image']);
            $stmt->bindParam('p_url', $params['url']);
            $stmt->bindParam('p_status', $params['status'], PDO::PARAM_INT);
            $stmt->bindParam('p_home', $params['home']);
            $stmt->bindParam('p_award', $params['award']);
            $stmt->bindParam('p_info', $params['info']);
            $stmt->bindParam('p_coachman', $params['coachman']);
            $stmt->execute();

            //Select output            
            $result     = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();            

            //Unset
            unset($stmt);

            //Return
            return (isset($result['result']) && $result['result']==1)?true:false;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return false;
        }//end try catch        
    }
    /**
     * delete comment
     */
    public function deleteComment($commentId)
    {
        try
        {
            //Get db
            $clubMaster = Fpt_Global::getClubMasterDB();
            $stmt       = $clubMaster->prepare('CALL sp_clubs_deleteComment(:p_commentid);');
            $stmt->bindParam('p_commentid', $commentId, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch();

            //Closed cursor
            $stmt->closeCursor();

            //Unset
            unset($stmt);

            //Return
            return (isset($result['result']) && $result['result']==1)?true:false;
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return false;
        }//end try catch
    }

    /**
     * Get list comment new
     */
    public function getListCommentNew($limit, $offset)
    {
        try
        {
            //Get db
            $clubSlave  = Fpt_Global::getClubSlaveDB();
            $stmt       = $clubSlave->prepare('CALL sp_clubs_getListComment(:p_limit, :p_offset, @p_rowcount);');
            $stmt->bindParam('p_limit', $limit, PDO::PARAM_INT);
            $stmt->bindParam('p_offset', $offset, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetchAll();

            //Closed cursor
            $stmt->closeCursor();

            //Select output
            $outputObj  = $clubSlave->query('SELECT @p_rowcount AS total');
            $arrTotal   = $outputObj->fetch();

            //Closed cursor
            $outputObj->closeCursor();

            //Unset
            unset($stmt);
            unset($outputObj);

            //Return
            return array('list'=>$result, 'total'=>$arrTotal['total']);
        }
        catch ( Exception $e )
        {
            ClubFavorite::logError($e);
            return array('list'=>null, 'total'=>0);
        }//end try catch
    }
}